import os

from flask import request, render_template, redirect, flash, url_for, jsonify, session
from sqlalchemy.exc import SQLAlchemyError
from werkzeug.datastructures import CombinedMultiDict

from apps import app, db
from apps.models import Publish, Author, Book, User
from apps.forms import PublishForm, AuthorForm, BookForm, RegisterForm, LoginForm
from sqlalchemy import func



@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'GET':
        form = RegisterForm()
        return render_template('reg.html', form=form)
    elif request.method == 'POST':
        form = RegisterForm(request.form)
        print(form)
        print(form.username.data)
        print(form.password.data)
        print(form.v_password.data)
        print(form.email.data)
        if form.validate_on_submit():
            username = form.username.data
            print(username)
            password = form.password.data
            email = form.email.data
            user = User(username=username, password=password, email=email)
            db.session.add(user)
            db.session.commit()
            return redirect(url_for('login'))
        else:
            return render_template('reg.html', form=form)


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'GET':
        form = LoginForm()
        return render_template('login.html', form=form)
    elif request.method == 'POST':
        form = LoginForm(request.form)
        if form.validate_on_submit():
            username = form.username.data
            password = form.password.data
            user = User.query.filter(User.username == username, User.password == password).first()
            if user:
                session['user_id'] = user.u_id
                return redirect(url_for('index'))
            return render_template('login.html', form=form)
        else:
            return render_template('login.html', form=form)


@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))




@app.route('/pub_list', methods=['GET'])
def pub_list():
    form = PublishForm()
    name = request.args.get('name')
    page = request.args.get('page', 1, type=int)
    if name:
        pagination = Publish.query.filter(Publish.name.ilike(f'%{name}%')).paginate(page=page, per_page=10,
                                                                                    error_out=False)
    else:
        pagination = Publish.query.paginate(page=page, per_page=10, error_out=False)
    pubs = pagination.items
    return render_template('publish/index.html', form=form,pubs=pubs, pagination=pagination, name=name)


@app.route('/pub_add', methods=['GET', 'POST'])
def pub_add():
    form = PublishForm()
    if form.validate_on_submit():
        v_name = Publish.query.filter(Publish.name == form.name.data).first()
        if v_name:
            flash('出版社已存在')
            return render_template('publish/add.html', form=form)
        else:
            pub = Publish(name=form.name.data, address=form.address.data, intro=form.intro.data)
            db.session.add(pub)
            db.session.commit()
            return redirect(url_for('pub_list'))
    return render_template('publish/add.html', form=form)


@app.route('/pub_edit/<int:p_id>', methods=['GET', 'POST'])
def pub_edit(p_id):
    if request.method == 'GET':
        pub_obj = Publish.query.filter(Publish.p_id == p_id).first()
        form = PublishForm()
        form.name.data = pub_obj.name
        form.address.data = pub_obj.address
        form.intro.data = pub_obj.intro
        return render_template('publish/edit.html', form=form)
    elif request.method == 'POST':
        form = PublishForm(request.form)
        if form.validate_on_submit():
            v_name = Publish.query.filter(Publish.name == form.name.data).first()
            if v_name:
                flash('出版社已存在')
                return render_template('publish/edit.html', form=form)
            else:
                pub = Publish.query.filter(Publish.p_id == p_id).first()
                pub.name = form.name.data
                pub.address = form.address.data
                pub.intro = form.intro.data
                db.session.commit()
                return redirect(url_for('pub_list'))
        else:
            return render_template('publish/edit.html', form=form)


@app.route('/pub_del', methods=['GET', 'POST'])
def pub_del():
    try:
        # print(request.values)
        # d = request.values.to_dict()
        # print(d)
        p_id = request.values.get('p_id')
        pub_obj = Publish.query.filter(Publish.p_id == p_id).first()
        db.session.delete(pub_obj)
        db.session.commit()
        return jsonify({'code': 200, 'message': '删除成功'})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({'code': 400, 'message': '删除失败'})


@app.route('/author_list', methods=['GET', 'POST'])
def author_list():
    form = AuthorForm()
    name = request.args.get('name')
    mobile = request.args.get('mobile')
    search_dict = {}
    if name:
        search_dict['name'] = name
    if mobile:
        search_dict['mobile'] = mobile
    page = request.args.get('page', 1, type=int)
    pagination = Author.query.filter_by(**search_dict).paginate(page=page, per_page=10, error_out=False)
    authors = pagination.items
    return render_template('author/index.html', form=form, authors=authors, pagination=pagination, name=name, mobile=mobile)


@app.route('/author_add', methods=['GET', 'POST'])
def author_add():
    form = AuthorForm()
    if form.validate_on_submit():
        author_obj = Author(name=form.name.data, mobile=form.mobile.data, age=form.age.data, address=form.address.data,
                            intro=form.intro.data)
        db.session.add(author_obj)
        db.session.commit()
        return redirect(url_for('author_list'))
    return render_template('author/add.html', form=form)


@app.route('/author_edit/<int:a_id>', methods=['GET', 'POST'])
def author_edit(a_id):
    if request.method == 'GET':
        author_obj = Author.query.filter(Author.a_id == a_id).first()
        form = AuthorForm()
        form.name.data = author_obj.name
        form.mobile.data = author_obj.mobile
        form.age.data = author_obj.age
        form.address.data = author_obj.address
        form.intro.data = author_obj.intro
        return render_template('author/edit.html', form=form)
    elif request.method == 'POST':
        form = AuthorForm(request.form)
        if form.validate_on_submit():
            author_obj = Author.query.filter(Author.a_id == a_id).first()
            author_obj.name = form.name.data
            author_obj.mobile = form.mobile.data
            author_obj.age = form.age.data
            author_obj.address = form.address.data
            author_obj.intro = form.intro.data
            db.session.commit()
            return redirect(url_for('author_list'))
        else:
            return render_template('author/edit.html', form=form)


@app.route('/author_del', methods=['GET', 'POST'])
def author_del():
    try:
        a_id = request.values.get('a_id')
        author_obj = Author.query.filter(Author.a_id == a_id).first()
        db.session.delete(author_obj)
        db.session.commit()
        return jsonify({'code': 200, 'message': '删除成功'})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({'code': 400, 'message': '删除失败'})


@app.route('/book_list', methods=['GET', 'POST'])
def book_list():
    if request.method == 'GET':
        form = BookForm()
        pub_id = request.args.get('publish')
        name = request.args.get('name')
        search_dict = {}
        if name:
            search_dict['name'] = name
        if pub_id and pub_id != '0':
            search_dict['pub_id'] = pub_id
        page = request.args.get('page', 1, type=int)
        pagination = Book.query.filter_by(**search_dict).paginate(page=page, per_page=10, error_out=False)
        books = pagination.items
        return render_template('book/index.html', form=form, books=books, pagination=pagination, name=name,
                               publish=pub_id)


@app.route('/book_add', methods=['GET', 'POST'])
def book_add():
    if request.method == 'GET':
        form = BookForm()
        return render_template('book/add.html', form=form)
    elif request.method == 'POST':
        form = BookForm(CombinedMultiDict([request.form, request.files]))
        print(form)
        if form.validate_on_submit():
            filepath = os.path.abspath(os.path.dirname(__file__))
            print(filepath)
            book = Book()
            book.name = form.name.data
            book.isbn = form.isbn.data
            book.price = form.price.data
            file = form.photo.data
            print(file)
            if file:
                filename = os.path.join(filepath, 'static/media/', file.filename)
                print(filename)
                file.save(filename)
                book.photo = file.filename
            book.pub_id = form.publish.data
            db.session.add(book)
            db.session.flush()
            b_id = book.b_id
            book_obj = Book.query.filter(Book.b_id == b_id).first()
            print(request.form.get('author'))
            select_authors = request.form.getlist('author')
            for i in select_authors:
                author_obj = Author.query.get(i)
                book_obj.back_author.append(author_obj)
            db.session.commit()
            return redirect(url_for('book_list'))
        else:
            return render_template('book/add.html', form=form)


@app.route('/book_edit/<int:b_id>', methods=['GET', 'POST'])
def book_edit(b_id):
    if request.method == 'GET':
        book_obj = Book.query.get(b_id)
        authorS = book_obj.back_author
        author_ids = [i.a_id for i in authorS]
        f = BookForm()
        f.name.data = book_obj.name
        f.isbn.data = book_obj.isbn
        f.price.data = book_obj.price
        f.photo.data = book_obj.photo
        publish_obj = book_obj.back_publish
        f.publish.data = publish_obj.p_id
        f.author.data = author_ids
        return render_template('book/edit.html', form=f, book=book_obj)
    elif request.method == 'POST':
        filepath = os.path.abspath(os.path.dirname(__file__))
        f = BookForm(CombinedMultiDict([request.form, request.files]))
        book_obj = Book.query.get(b_id)
        if f.validate_on_submit():
            book_obj.name = f.name.data
            book_obj.isbn = f.isbn.data
            file = f.photo.data
            if file:
                filename = os.path.join(filepath, 'static/media/', file.filename)
                file.save(filename)
                book_obj.photo = file.filename
            book_obj.price = f.price.data
            book_obj.pub_id = f.publish.data
            select_authors = request.form.getlist('author')
            for obj in book_obj.back_author:
                author = Author.query.get(obj.a_id)
                book_obj.back_author.remove(author)
            for i in select_authors:
                author = Author.query.get(i)
                book_obj.back_author.append(author)
            db.session.commit()
            return redirect(url_for('book_list'))
        else:
            return render_template('book/edit.html', form=f, book=book_obj)


@app.route('/book_del', methods=['GET', 'POST'])
def book_del():
    try:
        b_id = request.values.get('b_id')
        book_obj = Book.query.filter(Book.b_id == b_id).first()
        db.session.delete(book_obj)
        for obj in book_obj.back_author:
            author = Author.query.get(obj.a_id)
            book_obj.back_author.remove(author)
        db.session.commit()
        return jsonify({'code': 200, 'message': '删除成功'})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({'code': 400, 'message': '删除失败'})


@app.get('/')
def index():
    infos = Book.query.join(Publish, Book.pub_id == Publish.p_id).with_entities(Publish.name,
                                                                                func.count(Publish.name)).group_by(
        Publish.name).all()
    print(infos)
    pub_count = db.session.query(func.count(Publish.p_id)).scalar()
    author_count = db.session.query(func.count(Author.a_id)).scalar()
    book_count = db.session.query(func.count(Book.b_id)).scalar()
    labels = [info[0] for info in infos]
    datas = [info[1] for info in infos]
    return render_template('index.html', pub_count=pub_count, author_count=author_count, book_count=book_count,
                           labels=labels, datas=datas)





@app.route('/user_list', methods=['GET', 'POST'])
def user_list():
    form = RegisterForm()
    username = request.args.get('username')
    email = request.args.get('email')
    search_dict = {}
    if username:
        search_dict['username'] = username
    if email:
        search_dict['email'] = email
    page = request.args.get('page', 1, type=int)
    pagination = User.query.filter_by(**search_dict).paginate(page=int(request.args.get('page', 1)), per_page=10,error_out= False)
    users = pagination.items
    return render_template('user/index.html', form=form,users=users, pagination=pagination,username=username,email=email)


@app.route('/user_add', methods=['GET', 'POST'])
def user_add():
    if request.method == 'GET':
        form = RegisterForm()
        return render_template('user/add.html', form=form)
    elif request.method == 'POST':
        form = RegisterForm(request.form)
        if form.validate_on_submit():
            username = form.username.data
            print(username)
            password = form.password.data
            email = form.email.data
            user = User(username=username, password=password, email=email)
            db.session.add(user)
            db.session.commit()
            return redirect(url_for('user_list'))
        else:
            return render_template('user/add.html', form=form)


@app.route('/user_edit/<int:u_id>', methods=['GET', 'POST'])
def user_edit(u_id):
    if request.method == 'GET':
        user_obj = User.query.filter(User.u_id == u_id).first()
        form = RegisterForm()
        form.username.data = user_obj.username
        form.password.data = user_obj.password
        form.email.data = user_obj.email
        return render_template('user/edit.html', form=form)
    elif request.method == 'POST':
        form = RegisterForm(request.form)
        if form.validate_on_submit():
            user_obj = User.query.filter(User.u_id == u_id).first()
            form.username.data = user_obj.username
            form.password.data = user_obj.password
            form.email.data = user_obj.email
            db.session.commit()
            return redirect(url_for('user_list'))
        else:
            return render_template('user/edit.html', form=form)


@app.route('/user_del', methods=['GET', 'POST'])
def user_del():
    try:
        u_id = request.values.get('u_id')
        user_obj = User.query.filter(User.u_id == u_id).first()
        db.session.delete(user_obj)
        db.session.commit()
        return jsonify({'code': 200, 'message': '删除成功'})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({'code': 400, 'message': '删除失败'})